-- MySQL Script generated by MySQL Workbench
-- mar 28 abr 2020 10:36:39 CEST
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema webdb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `webdb` ;

-- -----------------------------------------------------
-- Schema webdb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `webdb` DEFAULT CHARACTER SET utf8mb4 ;
USE `webdb` ;


-- -----------------------------------------------------
-- Table `webdb`.`proposals_types`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `webdb`.`proposals_types` (
  `idproposals_types` INT NOT NULL DEFAULT 0,
  `type` VARCHAR(16) NOT NULL,
  `description` VARCHAR(250) NOT NULL,
  PRIMARY KEY (`idproposals_types`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `webdb`.`proposals`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `webdb`.`proposals` (
  `idproposals` INT NOT NULL AUTO_INCREMENT,
  `users_mail` VARCHAR(40) COLLATE 'utf8mb4_spanish_ci' NOT NULL,
  `title` VARCHAR(50) NOT NULL,
  `content` VARCHAR(1500) NOT NULL,
  `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expiration_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `proposals_type` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`idproposals`),
  INDEX `fk_proposals_users_idx` (`users_mail` ASC),
  INDEX `fk_proposals_proposals_types1_idx` (`proposals_type` ASC),
  CONSTRAINT `fk_proposals_users`
    FOREIGN KEY (`users_mail`)
    REFERENCES `webdb`.`users` (`mail`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_proposals_proposals_types1`
    FOREIGN KEY (`proposals_type`)
    REFERENCES `webdb`.`proposals_types` (`idproposals_types`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;


-- -----------------------------------------------------
-- Table `webdb`.`votes`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `webdb`.`votes` (
  `users_mail` VARCHAR(40) COLLATE 'utf8mb4_spanish_ci' NOT NULL,
  `proposals_idproposals` INT NOT NULL,
  `vote` INT(1) NOT NULL DEFAULT -1,
  `vote_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`users_mail`, `proposals_idproposals`),
  INDEX `fk_users_has_proposals_proposals1_idx` (`proposals_idproposals` ASC),
  INDEX `fk_users_has_proposals_users1_idx` (`users_mail` ASC),
  CONSTRAINT `fk_users_has_proposals_users1`
    FOREIGN KEY (`users_mail`)
    REFERENCES `webdb`.`users` (`mail`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_users_has_proposals_proposals1`
    FOREIGN KEY (`proposals_idproposals`)
    REFERENCES `webdb`.`proposals` (`idproposals`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `webdb`.`signers`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `webdb`.`signers` (
  `dni` VARCHAR(9) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `surname` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`dni`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `webdb`.`signatures`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `webdb`.`signatures` (
  `proposals_idproposals` INT NOT NULL,
  `signers_dni` VARCHAR(9) NOT NULL,
  `signature_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`proposals_idproposals`, `signers_dni`),
  INDEX `fk_proposals_has_signers_signers1_idx` (`signers_dni` ASC),
  INDEX `fk_proposals_has_signers_proposals1_idx` (`proposals_idproposals` ASC),
  CONSTRAINT `fk_proposals_has_signers_proposals1`
    FOREIGN KEY (`proposals_idproposals`)
    REFERENCES `webdb`.`proposals` (`idproposals`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_proposals_has_signers_signers1`
    FOREIGN KEY (`signers_dni`)
    REFERENCES `webdb`.`signers` (`dni`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;


-- -----------------------------------------------------
-- Table `webdb`.`communities`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `webdb`.`communities` (
  `idcommunity` INT NOT NULL AUTO_INCREMENT,
  `comunity` VARCHAR(45) NOT NULL,
  `users_mail` VARCHAR(40) COLLATE 'utf8mb4_spanish_ci' NOT NULL,
  PRIMARY KEY (`idcommunity`),
  INDEX `fk_communities_users1_idx` (`users_mail` ASC),
  CONSTRAINT `fk_communities_users1`
    FOREIGN KEY (`users_mail`)
    REFERENCES `webdb`.`users` (`mail`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `webdb`.`users_communities`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `webdb`.`users_communities` (
  `users_mail` VARCHAR(40) COLLATE 'utf8mb4_spanish_ci' NOT NULL,
  `communities_idcommunity` INT NOT NULL,
  PRIMARY KEY (`users_mail`, `communities_idcommunity`),
  INDEX `fk_users_has_communities_communities1_idx` (`communities_idcommunity` ASC),
  INDEX `fk_users_has_communities_users1_idx` (`users_mail` ASC),
  CONSTRAINT `fk_users_has_communities_users1`
    FOREIGN KEY (`users_mail`)
    REFERENCES `webdb`.`users` (`mail`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_users_has_communities_communities1`
    FOREIGN KEY (`communities_idcommunity`)
    REFERENCES `webdb`.`communities` (`idcommunity`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `webdb`.`proposals_communities`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `webdb`.`proposals_communities` (
  `proposals_idproposals` INT NOT NULL,
  `communities_idcommunity` INT NOT NULL,
  PRIMARY KEY (`proposals_idproposals`, `communities_idcommunity`),
  INDEX `fk_communities_has_proposals_proposals1_idx` (`proposals_idproposals` ASC),
  INDEX `fk_communities_has_proposals_communities1_idx` (`communities_idcommunity` ASC),
  CONSTRAINT `fk_communities_has_proposals_communities1`
    FOREIGN KEY (`communities_idcommunity`)
    REFERENCES `webdb`.`communities` (`idcommunity`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_communities_has_proposals_proposals1`
    FOREIGN KEY (`proposals_idproposals`)
    REFERENCES `webdb`.`proposals` (`idproposals`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Data for table `webdb`.`users`
-- -----------------------------------------------------
START TRANSACTION;
USE `webdb`;

COMMIT;


-- -----------------------------------------------------
-- Data for table `webdb`.`proposals_types`
-- -----------------------------------------------------
START TRANSACTION;
USE `webdb`;
INSERT INTO `webdb`.`proposals_types` (`idproposals_types`, `type`, `description`) VALUES (0, 'Votación pública', 'Accesible a todos los usuarios registrados');
INSERT INTO `webdb`.`proposals_types` (`idproposals_types`, `type`, `description`) VALUES (1, 'Votación privada', 'Accesible a todos los usuarios registrados. Se requiere conocer contraseña de acceso.');
INSERT INTO `webdb`.`proposals_types` (`idproposals_types`, `type`, `description`) VALUES (2, 'Votación secreta', 'Los usuarios no requieren registrarse, se genera un número limitado de claves que posibilitan el voto de forma exclusiva y privada. Cada clave puede ser usada una sola vez.');
INSERT INTO `webdb`.`proposals_types` (`idproposals_types`, `type`, `description`) VALUES (3, 'Firma', 'Acceso a voto a usuario no autenticados en donde únicamente se registrará su nombre, apellidos y DNI.');

COMMIT;


-- -----------------------------------------------------
-- Data for table `webdb`.`communities`
-- -----------------------------------------------------
START TRANSACTION;
USE `webdb`;
INSERT INTO `webdb`.`communities` (`idcommunity`, `comunity`, `users_mail`) VALUES (DEFAULT, 'GLOBAL', 'admin@web');

COMMIT;


-- -----------------------------------------------------
-- Data for table `webdb`.`users_communities`
-- -----------------------------------------------------
START TRANSACTION;
USE `webdb`;
INSERT INTO `webdb`.`users_communities` (`users_mail`, `communities_idcommunity`) VALUES ('user@web', 1);

COMMIT;

